/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
 //SELECT subjects.subjectName, professors.professor_name, groups.groupName, groups.state from groups, subjects, professors where groups.subjectKey=subjects.subjectKey and groups.ID_Professor=professors.ID_Professor;
package com.ControlEscolar.DAOs;

import com.ControlEscolar.Beans.Group;
import java.util.ArrayList;


/**
 *
 * @author Listico
 */
public class GroupDAO extends AbstractDAO <Group> {

    @Override
    public void add(Group group) throws DAOException {
                String sql = "INSERT INTO groups  values(\""+
                        group.getSubjectKey()+"\", "+
                        group.getID_Professor()+", "+"\"" +
                        group.getGroupName() +"\", "+"\""+
                        group.getState()+"\")";

        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }
    }

    @Override
    public int delete(String condition) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int update(Group group) throws DAOException {
        String sql="UPDATE groups SET state= \""+group.getState()+
                "\" where subjectKey = \""+group.getSubjectKey()+
                "\" and ID_Professor= "+group.getID_Professor()+
                " and groupName=\""+group.getGroupName()+"\"";
        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }

        return 0;
    }

    @Override
    public ArrayList<Group> query ()throws DAOException {
         try {
            String sql = "SELECT * FROM groups";
            return connection.executeQuery(sql, Group.class);
        } catch (Exception ex) {
            throw new DAOException();
        }
    }

    public Group queryByName(String groupName) throws DAOException{
        try {
            String sql = "SELECT * FROM groups where  groupName= \""+groupName+"\"";
            ArrayList<Group> groupQuery = connection.executeQuery(sql, Group.class);
             return getSpecificQuery(groupQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }
    }

    public ArrayList<Group> queryStudentGroupsAvailable (ArrayList <String> subjects)throws DAOException {
         try {
             int subjectKey;
             String sql = "SELECT * FROM groups where (";
            for(subjectKey=0; subjectKey<subjects.size()-1;subjectKey++){
                sql+="subjectKey = \""+subjects.get(subjectKey)+"\" or ";
            }
             sql+="subjectKey= \""+subjects.get(subjectKey)+"\") and state=\"A\"";
             return connection.executeQuery(sql, Group.class);
         } catch (Exception ex) {
            throw new DAOException();

        }
    }

}
